Last Update: February 5, 2022
DASHBOARD SETUP
Table of Contents
Summary
Q-LawE Dashboard provides a high-level overview of various tasks and processes across Q-LawE. The dashboard is driven by the userlist groups, meaning users can only see entries for groups they belong to. The entries in the dashboard provide data from a specific table summed into one easy-to-read line and allow for quick identification of any areas that need attention or improvement.
Understanding the Screen
The Dashboard is accessible from the Main Menu by clicking the button of the same name. The Dashboard will automatically show all active jobs for groups the user is assigned to.
When the Dashboard screen loads, all entries for that group will be listed. Click [Refresh] to calculate the current results.
- Group: Field for grouping items together by priority. If ‘Important’ is entered, this field will be highlighted red
- Subgroup: Field for grouping items together by category
- Descr: Brief description of the data being returned
- Alert: This field will be red or green, signifying if there are or are not any issues, respectively
- Days: Number of days to use in data calculation
- Thres_coun: Number of records that signify an issue
- #: Number of records returned
- Amount: If the data calculates a dollar total, it would be returned here, rounded to the nearest dollar
If the # field is greater than the Thres_coun field, that signifies an issue and the Alert field will be red.
Highlight an entry and click [View Detail] to see the data behind the number, such as a list of ourfiles or job scheduler jobs. This detail will vary depending on how the entry was setup.
Edit Dashboard
To edit/add/delete entries, click [Edit Dashboard] from the Dashboard Menu. This will bring up a grid of all dashboard entries. Use the Keyword field and Field dropdown to filter the grid if needed.
Click [Edit Record] to make changes. To add a new record, highlight any record and click [Add New] to create a duplicate record. Try choosing a record close to the new one being created if possible to help reduce the amount of changes needed.
- Active: Logical field, inactive records will not be refreshed but will still be visible
- Sorder: The sort order of the entries; filter the grid by usergroup to help determine the desired sort order
- Vgroup: A user defined primary organization field; enter ‘Important’ to have the field highlight on the Dashboard Menu
- Vsubgroup: A user defined secondary organization field
- Descr: A brief description of the data being viewed
- Usergroup: The userlist group this record pertains to; must match the userlist group exactly and should be all capital letters; entering a * in this field will enable the record to appear in all dashboard groups
- No_days: The number of days to be used in the query, if applicable
- Thres_coun: The number of returned records that signifies an issue
- Rec_count: This field will get autopopulated during the next refresh; can be zeroed out when adding new records
- Rec_amount: This field will get autopopulated during the next refresh; can be zeroed out when adding new records
- Alertcolor: always leave blank
- Stable: The main table that will be queried
- Sseledata: A query statement used to populate the dashboard count and amount fields on refresh
- Syntax: SELECT COUNT([record]) as recs, SUM([amount]) as amount FROM [table name] WHERE [conditions] into cursor ktemp noconsole
- This syntax is required; any instance of [example] should be replaced with the appropriate data
- Record: This should be an identifier for the data. If querying claim related data, this could be the ourfile or custfile field. If querying a system table, like the job scheduler (TS_JOBS), this could be jobid
- Amount: If querying data where a sum total would be helpful to know, include a numeric field here. The SUM([amount]) section can be omitted if a summed amount is not needed for the query
- Table name: Remote tables can be called with just the table name, i.e.,’ FROM debt’ or ‘FROM dlegal’; local tables will require a filepath, i.e., ‘FROM .\data\ts_jobs’
- Conditions: A standard where clause to filter which data should be returned; the no_days field can be used in the condition by calling ‘dashboard.no_days’ in the expression, i.e., ‘date <= DATE()-dashboard.no_days’ to find records that are older than the specified number of days
- Local tables will require FOX syntax while remote tables can use SQL syntax
- Sselelist: A query to populate the View Detail grid
- Syntax: SELECT [fields] FROM [table name] WHERE [conditions] into cursor ktemp noconsole
- This syntax is required; any instance of [example] should be replaced with the appropriate data
- Fields: The field names that should be returned; the standard is to use ‘*’ to return all fields in the table but specific fields can be used; separate each with a comma
- Table name: Remote tables can be called with just the table name, i.e.,’ FROM debt’ or ‘FROM dlegal’; local tables will require a filepath, i.e., ‘FROM .\data\ts_jobs’
- Conditions: a standard where clause to filter which data should be returned; the no_days field can be used in the condition by calling ‘dashboard.no_days’ in the expression, i.e., ‘date <= DATE()-dashboard.no_days’ to find records that are older than the specified number of days
- Local tables will require FOX syntax while remote tables can use SQL syntax
- Sqlwhere: For remote queries, where clause of the Sseledata field; only include portion between ‘WHERE’ and ‘into cursor ktemp noconsole’
- Sqlbig: Leave blank
- Runprog: Leave blank
- Lrefresh: Date/time of the last time this record was refreshed; can be cleared out for new records
- Lastedit: Date/time of last edit; will not be auto updated but can be manually updated for tracking purposes
- Username: Leave blank
Setting up new entries can be tricky at first so reach out to Q-LawE Support or your designated BSA for help if there are any issues.
User Setup
In the Member of groups field of the User Setup screen on the User Details tab, each group the user belongs to must be listed in this field, separated by commas. They should be entered in all capital letters.
Sample Entries
Here are a couple ideas of how the dashboard can be used as well as the setup for each.
Job Scheduler jobs on hold
This entry can be used to identify if there are any job scheduler jobs that have been on hold for more than a certain number of days. In this example, a firm wants to view jobs on hold for more than 3 days.
- No_days = 3
- Thres_coun = 0
- This way if there are any jobs on hold, the dashboard will alert us
- Stable = TS_JOBS
- Sseledata = SELECT COUNT(taskid) as recs FROM .\data\ts_jobs WHERE frequency = ‘DAILY’ and active = .t. and hold = .t. and date <= DATE()-dashboard.no_days into cursor ktemp noconsole
- This is using ‘dashboard.no_days’ in the condition. If the firm decides to view jobs on hold for one day instead of three, the no_days field can be updated without having to mess with the select statements.
- Sselelist = SELECT * FROM .\data\ts_jobs WHERE frequency = ‘DAILY’ and active = .t. and hold = .t. and date <= DATE()-dashboard.no_days into cursor ktemp noconsole
- Uses * to return all fields from the table
New PTPs created today
This entry will identify the number of PTPs created that day and the sum of the expected PTP amounts.
- No_days = 0
- Thres_count = 999
- By putting a very high number in this field, the alert column will remain green even when though records have been returned.
- Stable = CASEPTP
- Sseledata = SELECT COUNT(ourfile) as recs, sum(totexpect) as amount FROM caseptp WHERE active = .t. and date = DATE() into cursor ktemp noconsole
- Sselelist = SELECT * FROM caseptp WHERE active = .t. and date = DATE() into cursor ktemp noconsole
- SQLWhere = active = 1 and date = CAST(getdate() as date)
- CAST() is used to extract the date portion of the date/time returned from getdate()
Dollars Collected Today
This entry will identify the number of payments made that day and the sum of the payment amounts.
- No_days = 0
- Thres_count = 999
- By putting a very high number in this field, the alert column will remain green even when though records have been returned.
- Stable = TRANS
- Sseledata = SELECT COUNT(ourfile) as recs, sum(payment) as amount FROM trans WHERE trxdate = date() into cursor ktemp noconsole
- Sselelist = SELECT * FROM trans WHERE trxdate = date() into cursor ktemp noconsole
- SQLWhere = trxdate = CAST(getdate() as date)
- CAST() is used to extract the date portion of the date/time returned from getdate()
Tip: When testing a New Job in the Dashboard, it can be beneficial to inactivate any other jobs in the Edit Dashboard screen to ensure any errors received are specific to the new job you are adding. In the edit Dashboard screen there are 2 helpful buttons to check and uncheck all jobs easily.
- [V] Will check all jobs to be active.
- [unV] will uncheck all jobs to be inactive.
Reports
There are a couple dashboard reports available as well. These are separate from the dashboard entries in the grid. These reports are also hardcoded meaning users are unable to make any changes. Some reports can be filtered by state using the State dropdown on the right side of the screen or by time period using the Select period fields in the upper left-hand corner. Be sure to set the state or time period prior to selecting the report.
Tasks Due
This report will provide an overview of all tasks on the file grouped by taskcode and can be filtered by state. The first 9 columns pull information from the task itself or from the workflow to provide details of what is does. The next column, total_due, shows the total number of times the task is due on the file followed by due_today, which shows the number of those tasks with a due date of today, and then total_pastdue, which shows the number of those tasks with a due date older than today. The next 4 columns breakdown the past due tasks by 1 day, 2 days, 3 days and older than 3 days, respectively. The Future column is used as a breakpoint. After that will show the number of tasks coming due in 1 day, 2 days, 3 days, the next week and the next 30 days, respectively. Highlight a record and click [Select Record] or double click on it to see which files have the task on it.
Tasks Done
This report provides an overview of all tasks that has been completed within the 30 days and can be filtered by state. The first 9 columns pull information from the task itself or from the workflow to provide details of what is does. After that is the total number of tasks completed today and then the number of those that passed and failed. The Past column is used as a breakpoint to separate today’s completed tasks from those completed in the past. Next is the number of tasks completed yesterday, 2 days ago, 3 days ago within the last week and the total for the last 30 days. The final two columns show the number of those tasks that passed and the number that failed. Highlight a record and click [Select Record] or double click on it to see the files where the task was completed.
AMR Log
The AMR Log report uses the time period filter to review the AMR task results for the chosen time frame. The grid returns the AMR group, the consumer state, claim type, user who reviewed the file, the date of review, start time, end time and how long the review took. Next column will show if the task passed or failed. For failed tasks, see the memo in the fail_note column for details. Next is the ourfile for the file followed by the first 5 failure checklist items. Lastly there 5 document id and failure columns. If a docid was on the file but failed, the id is listed followed by the failure reason.
Worknow Log
The Worknow Log returns the worknow tasks worked within the specified time frame. First is the code for the worknow task and then ourfile it was worked on. Next is the date, time, length of time spent on the task and the user who worked it along with their role. After is the taskcode, the taskset and the due date of the task. There is also a column to indicate how past due the task was at the time of being worked. If the task was successfully completed, the Complete checkbox would be checked. If it failed, the exception task is listed followed by the exception reason.
Workflow Log
The Workflow Log returns all workflow items from within the specified time frame. It starts with the ourfile then the date, time and the user associated. The wf_id is the workflow id and the wf_item is the step within that workflow. The stage column indicates the stage the workflow itself and the sub column is a description of the workflow step. The DDASID column is the ID for the DDAS associated with the workflow move.